package com.zy.explore.db.infrastructure.plugins.multidbsupport.sqlchange.limit;

import com.zy.explore.db.infrastructure.plugins.multidbsupport.sqlchange.base.SqlOper;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static com.zy.explore.db.infrastructure.plugins.multidbsupport.sqlchange.base.SqlStringUtils.getSqlBlockBeforeIdx;
import static com.zy.explore.db.infrastructure.plugins.multidbsupport.sqlchange.base.SqlStringUtils.isBlank;


/**
 * 建类说明：sql限制工具类
 *
 * @author 中国印
 * @date 2022年02月16日 23:14:55
 */
public class SqlLimitUtils {
	/**
	 * limit匹配sql
	 * 	由于多行模式和limit匹配大小不兼容，所以将limit拆开
	 */
	private static final Pattern LIMIT_PATTERN = Pattern.compile("[\\)\\s]+([lL][iI][mM][iI][tT])\\s+(\\d+)(\\s*,\\s*(\\d+))?");
	
	/** 查找limit */
	public static List<SqlOper> find(final String sql){
		if(StringUtils.isEmpty(sql)){
			return new ArrayList<>();
		}
		List<SqlOper> sqlOperList = new ArrayList<>();
		
		int tempTableIdx = 0;
		
		Matcher matcher = LIMIT_PATTERN.matcher(sql);
		while(matcher.find()){
			//limit开始位置
			int limitIdx = matcher.start(1);
			//结束位置
			int endIdx = matcher.end(0);
			//数字
			String num1 = matcher.group(2);
			String num2 = null;
			if(matcher.groupCount() == 4){
				num2 = matcher.group(4);
			}
			//开始位置
			int ts = matcher.start(0);
			int selectIdx = getStrLastIdx(sql, "select", ts);
			int fromIdx = getStrLastIdx(sql, "from", ts);
			int whereIdx = getStrLastIdx(sql, "where", ts);
			int orderByIdx = getStrLastIdx(sql, "order by", ts);
			int groupByIdx = getStrLastIdx(sql, "group by", ts);
			//limit只有一个参数
			if(num2 == null){
				//存在order by或group by
				if((orderByIdx != -1 && orderByIdx > fromIdx) || (groupByIdx != -1 && groupByIdx > fromIdx)){
					//存在order by 或 group by : 增加嵌套
					
					//表别名
					String tableAlias1 = "temp"+(++tempTableIdx);
					sqlOperList.add(SqlOper.newAdd(selectIdx, "select "+tableAlias1+".* from ("));
					sqlOperList.add(SqlOper.newAdd(endIdx-1, ") "+tableAlias1+" where rownum <= " + num1));
					sqlOperList.add(SqlOper.newDelete(limitIdx, endIdx));
				}else if(whereIdx == -1){
					//不存在where：增加where和条件
					sqlOperList.add(SqlOper.newAdd(limitIdx, " where rownum <= " + num1));
					sqlOperList.add(SqlOper.newDelete(limitIdx, endIdx));
				}else{
					sqlOperList.add(SqlOper.newAdd(limitIdx, " and rownum <= " + num1));
					sqlOperList.add(SqlOper.newDelete(limitIdx, endIdx));
				}
			}else{//limit 存在两个参数
				//表别名
				String tableAlias1 = "temp"+(++tempTableIdx);
				String tableAlias2 = "temp"+(++tempTableIdx);
				
				String preStr = "select * from (select "+tableAlias1+".*, rownum as rn from (";
				String nextStr = ") "+tableAlias1+" where rownum <= "
						+(Integer.parseInt(num1) + Integer.parseInt(num2))+") "
						+tableAlias2+" where "+tableAlias2+".rn > "+num1;
				
				sqlOperList.add(SqlOper.newAdd(selectIdx, preStr));
				sqlOperList.add(SqlOper.newAdd(endIdx-1, nextStr));
				sqlOperList.add(SqlOper.newDelete(limitIdx, endIdx));
			}
		}
		return sqlOperList;
	}
	
	private static int getStrLastIdx(final String sql,String key,int stop){
		int idx;
		while(true){
			idx = lastIndexOfKey(sql,key, stop);
			if(idx == -1){
				break;
			}
			int b = getSqlBlockBeforeIdx(sql, idx, stop);
			if(b == -1){
				return idx;
			}
			stop = Math.min(b, idx);
		}
		return -1;
	}
	
	
	/**
	 * 忽略大小写和中间的空格查找关键字（从后往前查找）
	 * @param str 需要查找的字符串
	 * @param findKey 查找字符串
	 * @param stopPos 结束位置
	 * @return
	 */
	private static int lastIndexOfKey(final String str, final String findKey, int stopPos){
		stopPos = Math.min(stopPos, str.length());
		int fLen = findKey.length();
		if(fLen < 2){
			return StringUtils.lastIndexOfIgnoreCase(str,findKey, stopPos);
		}
		
		for(int i = stopPos-1;i>=0;i--){
			if(Character.toUpperCase(str.charAt(i)) != Character.toUpperCase(findKey.charAt(fLen-1))){
				//末尾字符不不相同
				continue;
			}
			if(i < findKey.length()-1 && Character.isLetterOrDigit(str.charAt(i+1))){
				//匹配字段后面是数字或字母，表示非单词
				continue;
			}
			
			int f = fLen-2;
			int s = i -1;
			boolean flag = true;
			while(f >=0 && s >= 0){
				char fc = Character.toUpperCase(findKey.charAt(f));
				char sc = Character.toUpperCase(str.charAt(s));
				//字符不相等 或者 都不是空格
				if(fc != sc && !(isBlank(fc) && isBlank(sc))){
					if(isBlank(fc) && isBlank(str.charAt(s+1))){
						//findStr多空格，str单空格
						f --;
						continue;
					}else if(isBlank(findKey.charAt(f+1)) && isBlank(sc)){
						//findStr单空格，str多空格
						s --;
						continue;
					}else{
						flag = false;
						break;
					}
				}
				f --;
				s --;
			}
			if(flag && f < 0){
				if(s >=0 && (Character.isLetterOrDigit(str.charAt(s)) || str.charAt(s) == '_')){
					//匹配字段前面是数字或字母，表示非单独
					continue;
				}
				return s +1;
			}
		}
		return -1;
	}
	

}
